﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using Web.Library.DomainObject.SQLServer;
using System.Data.SqlClient;
using Dapper;

using Web.Library.Enum;

namespace Web.Library.DataAccess.SQLServer
{

//    public partial class ProductsRepo
//    {        
//        public Categories LoadCategory(int? id, string connectionString)
//        {
//            using (var connection = base.GetConnection(connectionString))
//            {
//                return connection.Query<Categories>(
//                    @"SELECT 
//                        [CategoryID]
//                        ,[CategoryName]
//                        ,[Description]
//                        ,[Picture]
//                    FROM [dbo].[Categories] 
//                    WHERE [CategoryID] = @Id",
//                    new { Id = id }).SingleOrDefault();
//            }
//        }

//        public Suppliers LoadSupplier(int? id, string connectionString)
//        {
//            using (var connection = base.GetConnection(connectionString))
//            {
//                return connection.Query<Suppliers>(
//                    @"SELECT [SupplierID]
//                          ,[CompanyName]
//                          ,[ContactName]
//                          ,[ContactTitle]
//                          ,[Address]
//                          ,[City]
//                          ,[Region]
//                          ,[PostalCode]
//                          ,[Country]
//                          ,[Phone]
//                          ,[Fax]
//                          ,[HomePage]
//                      FROM [dbo].[Suppliers]
//                    WHERE [SupplierID] = @Id",
//                    new { Id = id }).SingleOrDefault();
//            }
//        }

//        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameter)
//        {
//            object _result = null;
//            using (IDbConnection conn = base.GetConnection(connectionString))
//            {
//                StringBuilder sbQuery = new StringBuilder();
//                switch (function)
//                {
//                    case SQL.Function.Aggregate.Max:
//                        sbQuery.AppendFormat(@"SELECT 
//                                                    MAX({0}) 
//                                                FROM 
//                                                Products with(nolock)
//                                                LEFT JOIN Suppliers with(nolock) on (Products.SupplierID = Suppliers.SupplierID)
//                                                LEFT JOIN Categories with(nolock) on (Products.CategoryID = Categories.CategoryID) {1}", column, parameter);
//                        break;
//                    case SQL.Function.Aggregate.Min:
//                        sbQuery.AppendFormat(@"SELECT 
//                                                    MIN({0}) 
//                                                FROM 
//                                                Products with(nolock)
//                                                LEFT JOIN Suppliers with(nolock) on (Products.SupplierID = Suppliers.SupplierID)
//                                                LEFT JOIN Categories with(nolock) on (Products.CategoryID = Categories.CategoryID) {1}", column, parameter);
//                        break;
//                    case SQL.Function.Aggregate.Distinct:
//                        sbQuery.AppendFormat(@"SELECT 
//                                                    Distinct({0}) 
//                                                FROM 
//                                                Products with(nolock)
//                                                LEFT JOIN Suppliers with(nolock) on (Products.SupplierID = Suppliers.SupplierID)
//                                                LEFT JOIN Categories with(nolock) on (Products.CategoryID = Categories.CategoryID) {1}", column, parameter);
//                        break;
//                    case SQL.Function.Aggregate.Count:
//                        sbQuery.AppendFormat(@"SELECT 
//                                                    Count({0}) 
//                                                FROM 
//                                                Products with(nolock)
//                                                LEFT JOIN Suppliers with(nolock) on (Products.SupplierID = Suppliers.SupplierID)
//                                                LEFT JOIN Categories with(nolock) on (Products.CategoryID = Categories.CategoryID) {1}", column, parameter);
//                        break;
//                    case SQL.Function.Aggregate.Sum:
//                        sbQuery.AppendFormat(@"SELECT 
//                                                    SUM({0}) 
//                                                FROM 
//                                                Products with(nolock)
//                                                LEFT JOIN Suppliers with(nolock) on (Products.SupplierID = Suppliers.SupplierID)
//                                                LEFT JOIN Categories with(nolock) on (Products.CategoryID = Categories.CategoryID) {1}", column, parameter);
//                        break;
//                    case SQL.Function.Aggregate.Avg:
//                        sbQuery.AppendFormat(@"SELECT 
//                                                    Avg({0}) 
//                                                FROM 
//                                                Products with(nolock)
//                                                LEFT JOIN Suppliers with(nolock) on (Products.SupplierID = Suppliers.SupplierID)
//                                                LEFT JOIN Categories with(nolock) on (Products.CategoryID = Categories.CategoryID) {1}", column, parameter);
//                        break;
//                    default:
//                        // do nothing 
//                        break;
//                }

//                try
//                {
//                    conn.Open();
//                    _result = conn.ExecuteScalar(sbQuery.ToString());
//                }
//                catch (Exception ex)
//                {
//                    throw (ex);
//                }
//                finally
//                {
//                    if (conn.State == ConnectionState.Open) conn.Close();
//                }
//            }
//            return _result;
//        }

//        public List<Products> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameter)
//        {
//            List<Products> tt = new List<Products>();
//            using (IDbConnection conn = base.GetConnection(connectionString))
//            {
//                int startRow = ((currentPage - 1) * pageSize) + 1;
//                int endRow = (startRow + pageSize);

//                StringBuilder sbQuery = new StringBuilder();
//                sbQuery.AppendFormat(" WITH result_set AS ");
//                sbQuery.AppendFormat(" ( ");
//                sbQuery.AppendFormat("    SELECT ");
//                sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
//                sbQuery.AppendFormat("      ProductID, ProductName, Products.SupplierID, Products.CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued "); 
//                sbQuery.AppendFormat("    FROM "); 
//                sbQuery.AppendFormat("      dbo.Products with(nolock) ");
//                sbQuery.AppendFormat("    LEFT JOIN Suppliers with(nolock) on (Products.SupplierID = Suppliers.SupplierID)");
//                sbQuery.AppendFormat("    LEFT JOIN Categories with(nolock) on (Products.CategoryID = Categories.CategoryID) ");
//                sbQuery.AppendFormat("    {0} ", parameter);
//                sbQuery.AppendFormat(" ) ");
//                sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

//                try
//                {
//                    conn.Open();
//                    tt = conn.Query<Products>(sbQuery.ToString()).ToList<Products>();
//                }
//                catch (Exception ex)
//                {
//                    throw (ex);
//                }
//                finally
//                {
//                    if (conn.State == ConnectionState.Open) conn.Close();
//                }
//            }

//            return tt;
//        }

//    }
}
